package ServerConnection;

import Message.Relationship;

import java.sql.*;
import java.util.ArrayList;

public class DatabaseServer {
    private static final String SQL_URL = "jdbc:mysql://120.77.179.191:3306/QQ?serverTimezone=Asia/Shanghai";
    private static final String SQL_USERNAME = "root";
    private static final String SQL_PASSWORD = "lzsb1234";
    private static Connection sqlConnection;
    private static Statement sqlStatement;

    private ResultSet sqlQueryResultSet;

    public DatabaseServer() {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            sqlConnection = DriverManager.getConnection(SQL_URL, SQL_USERNAME, SQL_PASSWORD);
            sqlStatement = sqlConnection.createStatement();
            sqlStatement.execute("USE QQ;");
            System.out.println("数据库服务成功启动!");

        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }

    public boolean doLogin(String userId, String userPassword) {
        try {
            sqlQueryResultSet = sqlStatement.executeQuery(String.format("SELECT *FROM UserAccount WHERE Uid='%s' and psw='%s';", userId, userPassword));
            if (sqlQueryResultSet.next()) {
                String getUid = sqlQueryResultSet.getString("Uid");
                if (getUid.equals(userId)) {
                    return true;
                }
            }
        } catch (SQLException e) {
            //这里是空查询的处理片段，暂时不做处理
            e.printStackTrace();
            return false;
        }
        return false;
    }

    public boolean doExecute(String statement) {
        try {
            sqlStatement.execute(statement);
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;

        }
    }    public boolean doSignUp(String statement) {
        try {
            sqlStatement.execute(statement);
            return true;
        } catch (SQLException e) {
            System.out.println("账号已经存在，已拒绝注册请求！");
            return false;

        }
    }

    public ResultSet returnResultSet(String sql) throws SQLException {
        ResultSet rs = null;
        Statement statement = sqlConnection.createStatement();
        try {
            rs = statement.executeQuery(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rs;
    }

    public ArrayList<String> returnUserFrom(String sql) throws SQLException {
        ResultSet rs = null;
        ArrayList<String> userIdInDatabaseFromArray = new ArrayList<>();
        Statement statement = sqlConnection.createStatement();

        int numberOfHaveSendedMessageUser = 0;
        try {
            rs = statement.executeQuery(sql);
            while (rs.next()){
                numberOfHaveSendedMessageUser++;
                System.out.println("numberOfHaveSendedMessageUser: "+numberOfHaveSendedMessageUser);
                userIdInDatabaseFromArray.add(rs.getString("userIdInDatabaseFrom"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return userIdInDatabaseFromArray;
    }

    /**
     * 该方法实现批量插入好友关系
     * */
    public boolean bulkInsertRelationship(String sql, ArrayList<Relationship> relationshipArrayList) throws SQLException {
        sqlConnection.setAutoCommit(false);// 关闭事务
        java.sql.PreparedStatement preparedStatement = sqlConnection.prepareStatement(sql); //PreparedStatement与Statement类似，但似乎更安全更高效
        for (Relationship relationship : relationshipArrayList) {
            try {
                preparedStatement.setString(1, relationship.getUserId1());
                preparedStatement.setString(2, relationship.getUserId2());
                preparedStatement.addBatch();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        try {
            preparedStatement.executeBatch();//执行给定的SQL语句，该语句可能返回多个结果
            sqlConnection.commit();//提交事务
            sqlConnection.setAutoCommit(true);//开启事务
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
            sqlConnection.setAutoCommit(true);//开启事务
            return false;
        }
    }

    /**
     * 该方法实现批量更新Message已被转发
     * */
    public boolean bulkUpdateMessageResponse(ArrayList<Integer> AccountSendMessageInfoId) throws SQLException {
        sqlConnection.setAutoCommit(false);// 关闭事务
        for (Integer integer : AccountSendMessageInfoId) {
            try {
                sqlStatement.addBatch("update Message set Message.Response = 1 where Message.MessageId = "+integer.intValue());
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        try {
            sqlStatement.executeBatch();//执行给定的SQL语句，该语句可能返回多个结果
            sqlConnection.commit();//提交事务
            sqlConnection.setAutoCommit(true);//开启事务
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
            sqlConnection.setAutoCommit(true);//开启事务
            return false;
        }
    }
}
